package com.timeDeal.service;




import com.alibaba.druid.util.StringUtils;
import com.timeDeal.bean.JiaoyiQingqiuBean;
import com.timeDeal.bean.PageList;
import com.timeDeal.util.DButil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by 叼爸爸 on 2016/9/6.
 */
public class JiaoyiQingqiuServicce {
    public  boolean tijiaoJiaoYiQingqiu(JiaoyiQingqiuBean jq  ){
        Connection cn = DButil.getConnection();
        String mysql="insert into  jiaoyiqingqiu (jiaoyiqingqiuid ,xuqiufabuid,tijiaorenid,qingqiuduixiangid )"
              +" values(?,?,?,?)";
        PreparedStatement ps=null;
        ResultSet rs=null;
        try{
            ps=cn.prepareStatement(mysql);
            ps.setInt(1,DButil.getZhuJianMax("jiaoyiqingqiuid","jiaoyiqingqiu"));
            ps.setInt(2,jq.getXuqiufabuid());
            ps.setInt(3,jq.getTijiaorenid());
            ps.setInt(4,jq.getQingqiuduixiangid());
                  ps.execute();
            return true ;
            }
          catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(ps!=null){
                    ps.close();
                }
                if(rs!=null){
                    rs.close();
                }
                cn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return  false ;
    }
    public  boolean tijiaoJiaoYiQingqiuByjq(JiaoyiQingqiuBean jq  ){
        Connection cn = DButil.getConnection();
        String mysql="insert into  jiaoyiqingqiu (jiaoyiqingqiuid ,shijianchushouid,tijiaorenid,qingqiuduixiangid )"
                +" values(?,?,?,?)";
        PreparedStatement ps=null;
        ResultSet rs=null;
        try{
            ps=cn.prepareStatement(mysql);
            ps.setInt(1,DButil.getZhuJianMax("jiaoyiqingqiuid","jiaoyiqingqiu"));
            ps.setInt(2,jq.getShijianchushouid());
            ps.setInt(3,jq.getTijiaorenid());
            ps.setInt(4,jq.getQingqiuduixiangid());
            ps.execute();
            return true ;
        }
        catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(ps!=null){
                    ps.close();
                }
                if(rs!=null){
                    rs.close();
                }
                cn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return  false ;
    }
        public PageList getQingqiuByid(int cu , int id){
        Connection connection = DButil.getConnection();
        String mysql="select count( *)   from  jiaoyiqingqiu   where  ( qingqiuduixiangid=? or tijiaorenid=?)  and shijianchushouid is NULL "   ;
        int pagesize = PageList.pagesize ;
        String mysql2="select *   from  jiaoyiqingqiu   where (qingqiuduixiangid=? or tijiaorenid=?)  and shijianchushouid is NULL  limit   "+ (cu*pagesize-pagesize)+", "+pagesize   ;
        PageList  pageList =  new PageList() ;
        List<Object> list =new ArrayList<>() ;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try{
            ps=connection.prepareStatement(mysql);
            ps.setInt(1,id);
            ps.setInt(2,id);
            rs = ps.executeQuery();
            if (rs.next()){
            }
            pageList.setTotalpage(rs.getInt(1)%pagesize==0?rs.getInt(1)/pagesize:rs.getInt(1)/pagesize+1) ;
            pageList.setCurrentpage(cu);
            ps.clearParameters();
            ps=connection.prepareStatement(mysql2);
            ps.setInt(1,id);
            ps.setInt(2,id);
            rs=ps.executeQuery() ;
            while (rs.next()){
                JiaoyiQingqiuBean jiaoyiQingqiuBean = new JiaoyiQingqiuBean() ;
                jiaoyiQingqiuBean.setJiaoyiqingqiuid(rs.getInt("jiaoyiqingqiuid"));
                jiaoyiQingqiuBean.setXuqiufabuid(rs.getInt("xuqiufabuid"));
                jiaoyiQingqiuBean.setTijiaorenid(rs.getInt("tijiaorenid"));
                jiaoyiQingqiuBean.setQingqiuduixiangid(rs.getInt("qingqiuduixiangid"));
                list.add(jiaoyiQingqiuBean);
            }
            pageList.setList(list);
            return  pageList ;
        }
        catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(ps!=null){
                    ps.close();
                }
                if(rs!=null){
                    rs.close();
                }
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return  null ;
    }
    public PageList getQingqiuByidAndShijian(int cu , int id){
        Connection connection = DButil.getConnection();
        String mysql="select count( *)   from  jiaoyiqingqiu   where  ( qingqiuduixiangid=? or tijiaorenid=?) and xuqiufabuid is NULL "   ;
        int pagesize = PageList.pagesize ;
        String mysql2="select *   from  jiaoyiqingqiu   where (qingqiuduixiangid=? or tijiaorenid=?)  and xuqiufabuid is NULL  limit   "+ (cu*pagesize-pagesize)+", "+pagesize   ;
        PageList  pageList =  new PageList() ;
        List<Object> list =new ArrayList<>() ;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try{
            ps=connection.prepareStatement(mysql);
            ps.setInt(1,id);
            ps.setInt(2,id);
            rs = ps.executeQuery();
            if (rs.next()){
            }
            pageList.setTotalpage(rs.getInt(1)%pagesize==0?rs.getInt(1)/pagesize:rs.getInt(1)/pagesize+1) ;
            pageList.setCurrentpage(cu);
            ps.clearParameters();
            ps=connection.prepareStatement(mysql2);
            ps.setInt(1,id);
            ps.setInt(2,id);
            rs=ps.executeQuery() ;
            while (rs.next()){
                JiaoyiQingqiuBean jiaoyiQingqiuBean = new JiaoyiQingqiuBean() ;
                jiaoyiQingqiuBean.setJiaoyiqingqiuid(rs.getInt("jiaoyiqingqiuid"));
                jiaoyiQingqiuBean.setShijianchushouid(rs.getInt("shijianchushouid"));
                jiaoyiQingqiuBean.setTijiaorenid(rs.getInt("tijiaorenid"));
                jiaoyiQingqiuBean.setQingqiuduixiangid(rs.getInt("qingqiuduixiangid"));
                list.add(jiaoyiQingqiuBean);
            }
            pageList.setList(list);
            return  pageList ;
        }
        catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(ps!=null){
                    ps.close();
                }
                if(rs!=null){
                    rs.close();
                }
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return  null ;
    }
    public boolean Tongyi(JiaoyiQingqiuBean jiaoyiQingqiuBean) {
        Connection conn = DButil.getConnection();
        String mysql = "update xuqiufabu set yifangid=? ,zhuangtai=?  where xuqiufabuid=?";
        String mysq2 = "delete from  jiaoyiqingqiu   where jiaoyiqingqiuid=?";
        PreparedStatement ps = null;
        ResultSet  rs = null;
        boolean re =false ;
        try {
                System.out.println(mysql);
                System.out.println(jiaoyiQingqiuBean.getTijiaorenid()+"SSSSSSSSSSSSSSSSSSS");
                System.out.println(mysql);
                ps = conn.prepareStatement(mysql);
                ps.setInt(1, jiaoyiQingqiuBean.getTijiaorenid());
                ps.setString(2, "交易中");
                ps.setInt(3, jiaoyiQingqiuBean.getXuqiufabuid());
                re = ps.execute();
                ps.clearParameters();
                System.out.println(mysq2);
                ps = conn.prepareStatement(mysq2);
                ps.setInt(1, jiaoyiQingqiuBean.getJiaoyiqingqiuid());
                re = ps.execute();
            return  true;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }
    public JiaoyiQingqiuBean getAQingqiuByid( int id){
        Connection connection = DButil.getConnection();
        String mysql="select *   from  jiaoyiqingqiu   where jiaoyiqingqiuid=? "  ;
        PreparedStatement ps=null;
        ResultSet rs=null;
        JiaoyiQingqiuBean jiaoyiQingqiuBean = new JiaoyiQingqiuBean() ;
        try{
            ps=connection.prepareStatement(mysql);
            ps.setInt(1,id);
            rs = ps.executeQuery();
            while (rs.next()){
                jiaoyiQingqiuBean.setJiaoyiqingqiuid(rs.getInt("jiaoyiqingqiuid"));
                jiaoyiQingqiuBean.setXuqiufabuid(rs.getInt("xuqiufabuid"));
                jiaoyiQingqiuBean.setTijiaorenid(rs.getInt("tijiaorenid"));
                System.out.println(rs.getInt("qingqiuduixiangid")+"AAAAAAAAAAAAAA");
                System.out.println(rs.getInt("tijiaorenid")+"AAAAAAAAAAAAAA");
                System.out.println(rs.getInt("jiaoyiqingqiuid")+"AAAAAAAAAAAAAA");
                System.out.println(rs.getInt("xuqiufabuid")+"AAAAAAAAAAAAAA");
                jiaoyiQingqiuBean.setQingqiuduixiangid(rs.getInt("qingqiuduixiangid"));

            }

            return  jiaoyiQingqiuBean ;
        }
        catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(ps!=null){
                    ps.close();
                }
                if(rs!=null){
                    rs.close();
                }
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return  null ;
    }
    public boolean  buTongyi(JiaoyiQingqiuBean jiaoyiQingqiuBean) {
        Connection conn = DButil.getConnection();
        String mysq2 = "delete from  jiaoyiqingqiu   where jiaoyiqingqiuid=?";
        PreparedStatement ps = null;
        ResultSet  rs = null;
        boolean re =false ;
        try {
            ps = conn.prepareStatement(mysq2);
            ps.setInt(1, jiaoyiQingqiuBean.getQingqiuduixiangid());
            re = ps.execute();
            return  true;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

}
